﻿using System;
using System.Data;
using System.Data.OleDb;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 数据库操作管理类(OleDb)
    /// </summary>
    public class DOleDb : IDisposable
    {
        #region 类成员定义

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        protected internal string mStrConnectionString = null;

        /// <summary>
        /// 数据库连接对象(用于事务处理)
        /// </summary>
        protected internal OleDbConnection mConnection = null;

        /// <summary>
        /// 数据库事务处理对象(用于事务处理)
        /// </summary>
        protected internal OleDbTransaction mTransaction = null;

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public DOleDb(string pStrConnectionString)
        {
            mStrConnectionString = pStrConnectionString;
        }

        #endregion

        #region 查询命令

        /// <summary>
        /// 执行查询命令
        /// </summary>
        public DataSet ExecuteQuery(string pQueryCommand)
        {
            return this.ExecuteQuery(pQueryCommand, null);
        }

        public DataSet ExecuteQuery(string pQueryCommand, params object[] pInParams)
        {
            if (mStrConnectionString == null) { return null; }
            DataSet refDataSet = new DataSet(); // 需要返回结果DataSet/null
            OleDbConnection myConnection = (mTransaction == null) ? new OleDbConnection(mStrConnectionString) : mConnection;
            using (OleDbDataAdapter myExecuteQueryDataAdapter = new OleDbDataAdapter())
            {
                // 设置查询命令及参数集
                myExecuteQueryDataAdapter.SelectCommand = new OleDbCommand(pQueryCommand, myConnection, mTransaction);
                this.SetCommandParams(myExecuteQueryDataAdapter.SelectCommand, pInParams, null, null);
                try
                {
                    // 开始执行查询命令
                    myExecuteQueryDataAdapter.Fill(refDataSet);
                }
                catch
                {
                    refDataSet = null;
                    if (mTransaction != null)
                    {
                        // 自动回滚事务
                        mTransaction.Rollback();
                        this.Dispose();
                    }
                }
            }
            if (mTransaction == null) { myConnection.Dispose(); }
            return refDataSet;
        }

        /// <summary>
        /// 执行查询命令
        /// </summary>
        public bool ExecuteFill(DataTable pRefTable, string pQueryCommand)
        {
            return this.ExecuteFill(pRefTable, pQueryCommand, null);
        }

        public bool ExecuteFill(DataTable pRefTable, string pQueryCommand, params object[] pInParams)
        {
            if (mStrConnectionString == null) { return false; }
            bool isSucceed = true; // 需要返回成功/失败
            // 清空pRefTable记录
            pRefTable.Rows.Clear();
            OleDbConnection myConnection = (mTransaction == null) ? new OleDbConnection(mStrConnectionString) : mConnection;
            using (OleDbDataAdapter myExecuteFillDataAdapter = new OleDbDataAdapter())
            {
                // 设置查询命令及参数集
                myExecuteFillDataAdapter.SelectCommand = new OleDbCommand(pQueryCommand, myConnection, mTransaction);
                this.SetCommandParams(myExecuteFillDataAdapter.SelectCommand, pInParams, null, null);
                try
                {
                    // 开始执行查询命令
                    myExecuteFillDataAdapter.Fill(pRefTable);
                }
                catch
                {
                    isSucceed = false;
                    if (mTransaction != null)
                    {
                        // 自动回滚事务
                        mTransaction.Rollback();
                        this.Dispose();
                    }
                }
            }
            if (mTransaction == null) { myConnection.Dispose(); }
            return isSucceed;
        }

        #endregion

        #region 批更新命令

        /// <summary>
        /// 执行批添加命令
        /// </summary>
        public int ExecuteAdd(DataTable pRefTable, string pAddInsCmd, params object[] pInsInParams)
        {
            DataTable myTable = pRefTable.Copy();
            myTable.AcceptChanges();
            foreach (DataRow myDataRow in myTable.Rows)
            {
                myDataRow.SetAdded(); // 置为添加状态
            }
            return this.ExecuteUpdate(myTable, pAddInsCmd, pInsInParams, null, "", null, "", null);
        }

        /// <summary>
        /// 执行批更新命令
        /// </summary>
        public int ExecuteUpdate(DataTable pRefTable, string pUpdateUpdCmd, params object[] pUpdInParams)
        {
            return this.ExecuteUpdate(pRefTable, "", null, null, pUpdateUpdCmd, pUpdInParams, "", null);
        }

        public int ExecuteUpdate(DataTable pRefTable, string pUpdateInsCmd, object[] pInsInParams, object[] pInsOutParams)
        {
            return this.ExecuteUpdate(pRefTable, pUpdateInsCmd, pInsInParams, pInsOutParams, "", null, "", null);
        }

        public int ExecuteUpdate(DataTable pRefTable, string pUpdateInsCmd, object[] pInsInParams, string pUpdateUpdCmd, object[] pUpdInParams)
        {
            return this.ExecuteUpdate(pRefTable, pUpdateInsCmd, pInsInParams, null, pUpdateUpdCmd, pUpdInParams, "", null);
        }

        public int ExecuteUpdate(DataTable pRefTable, string pUpdateInsCmd, object[] pInsInParams, object[] pInsOutParams, string pUpdateUpdCmd, object[] pUpdInParams)
        {
            return this.ExecuteUpdate(pRefTable, pUpdateInsCmd, pInsInParams, pInsOutParams, pUpdateUpdCmd, pUpdInParams, "", null);
        }

        public int ExecuteUpdate(DataTable pRefTable, string pUpdateInsCmd, object[] pInsInParams, string pUpdateUpdCmd, object[] pUpdInParams, string pUpdateDelCmd, object[] pDelInParams)
        {
            return this.ExecuteUpdate(pRefTable, pUpdateInsCmd, pInsInParams, null, pUpdateUpdCmd, pUpdInParams, pUpdateDelCmd, pDelInParams);
        }

        public int ExecuteUpdate(DataTable pRefTable, string pUpdateInsCmd, object[] pInsInParams, object[] pInsOutParams, string pUpdateUpdCmd, object[] pUpdInParams, string pUpdateDelCmd, object[] pDelInParams)
        {
            if (mStrConnectionString == null) { return -1; }
            int isSucceed = 0; // 需要返回0/-1
            DataTable myTable = pRefTable.GetChanges();
            if (myTable != null) // 有需要更新的数据
            {
                OleDbConnection myConnection = (mTransaction == null) ? new OleDbConnection(mStrConnectionString) : mConnection;
                OleDbTransaction myTransaction = (mTransaction == null) ? null : mTransaction;
                using (OleDbDataAdapter myExecuteUpdateDataAdapter = new OleDbDataAdapter())
                {
                    if (mTransaction == null)
                    {
                        try
                        {
                            // 开始事务处理
                            myConnection.Open();
                            myTransaction = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                        }
                        catch
                        {
                            isSucceed = -1;
                        }
                    }
                    if (isSucceed == 0)
                    {
                        // 设置批更新命令及参数集,自动识别是表列名还是数据值
                        myExecuteUpdateDataAdapter.SelectCommand = new OleDbCommand("", myConnection, myTransaction);
                        myExecuteUpdateDataAdapter.InsertCommand = new OleDbCommand(pUpdateInsCmd, myConnection, myTransaction);
                        myExecuteUpdateDataAdapter.UpdateCommand = new OleDbCommand(pUpdateUpdCmd, myConnection, myTransaction);
                        myExecuteUpdateDataAdapter.DeleteCommand = new OleDbCommand(pUpdateDelCmd, myConnection, myTransaction);
                        this.SetCommandParams(myExecuteUpdateDataAdapter.InsertCommand, pInsInParams, pInsOutParams, myTable);
                        this.SetCommandParams(myExecuteUpdateDataAdapter.UpdateCommand, pUpdInParams, null, myTable);
                        this.SetCommandParams(myExecuteUpdateDataAdapter.DeleteCommand, pDelInParams, null, myTable);
                        try
                        {
                            // 开始执行批更新命令
                            myExecuteUpdateDataAdapter.Update(myTable);
                            if (mTransaction == null)
                            {
                                // 提交事务处理
                                myTransaction.Commit();
                            }
                        }
                        catch
                        {
                            if (mTransaction == null)
                            {
                                // 回滚事务处理
                                myTransaction.Rollback();
                            }
                            isSucceed = -1;
                            if (mTransaction != null)
                            {
                                // 自动回滚事务
                                mTransaction.Rollback();
                                this.Dispose();
                            }
                        }
                        if (isSucceed == 0)
                        {
                            if (pInsOutParams != null)
                            {
                                // 返回插入的输出参数值
                                int myIndex = 0; // 序号
                                foreach (DataRow dr in pRefTable.Rows)
                                {
                                    if (dr.RowState != DataRowState.Unchanged) // 已改变的行
                                    {
                                        if (dr.RowState == DataRowState.Added) // 新添加的行
                                        {
                                            for (int i = 0; i < pInsOutParams.Length; i++)
                                            {
                                                // 批更新的插入输出参数必定是表列名
                                                dr[(string)pInsOutParams[i]] = myTable.Rows[myIndex][(string)pInsOutParams[i]];
                                            }
                                        }
                                        myIndex++;
                                    }
                                }
                            }
                            pRefTable.AcceptChanges();
                        }
                        if (mTransaction == null && myTransaction != null) { myTransaction.Dispose(); }
                    }
                }
                if (mTransaction == null) { myConnection.Dispose(); }
            }
            return isSucceed;
        }

        #endregion

        #region 执行命令

        /// <summary>
        /// 执行命令
        /// </summary>
        public int Execute(string pExecuteCommand)
        {
            return this.Execute(pExecuteCommand, null, null);
        }

        public int Execute(string pExecuteCommand, params object[] pInParams)
        {
            return this.Execute(pExecuteCommand, null, pInParams);
        }

        public int Execute(string pExecuteCommand, object[] pOutParams, params object[] pInParams)
        {
            if (mStrConnectionString == null) { return -1; }
            int isSucceed = 0; // 需要返回操作的行数/-1
            OleDbConnection myConnection = (mTransaction == null) ? new OleDbConnection(mStrConnectionString) : mConnection;
            using (OleDbCommand myExecuteCommand = new OleDbCommand(pExecuteCommand, myConnection, mTransaction))
            {
                // 设置命令及参数集
                this.SetCommandParams(myExecuteCommand, pInParams, pOutParams, null);
                try
                {
                    // 开始执行命令
                    if (mTransaction == null) { myConnection.Open(); }
                    isSucceed = myExecuteCommand.ExecuteNonQuery();
                    if (mTransaction == null) { myConnection.Close(); }
                    if (myExecuteCommand.CommandType == CommandType.StoredProcedure)
                    {
                        isSucceed = 0; // 存储过程返回0
                    }
                }
                catch
                {
                    isSucceed = -1;
                    if (mTransaction != null)
                    {
                        // 自动回滚事务
                        mTransaction.Rollback();
                        this.Dispose();
                    }
                }
                if (isSucceed >= 0)
                {
                    if (pOutParams != null)
                    {
                        // 返回输出参数值
                        for (int i = 0; i < pOutParams.Length; i++)
                        {
                            pOutParams[i] = myExecuteCommand.Parameters["@OP" + i.ToString()].Value;
                        }
                    }
                }
            }
            if (mTransaction == null) { myConnection.Dispose(); }
            return isSucceed;
        }

        #endregion

        #region 内部函数

        /// <summary>
        /// 设置Command对象相关参数
        /// </summary>
        /// <param name="pRefCommand">需要设置的Command对象</param>
        /// <param name="pInParams">输入参数集</param>
        /// <param name="pOutParams">输出参数集</param>
        /// <param name="pTable">DataTable,用于自动识别表列名</param>
        private void SetCommandParams(OleDbCommand pRefCommand, object[] pInParams, object[] pOutParams, DataTable pTable)
        {
            // 设置OleDb命令类型
            pRefCommand.CommandType = (pRefCommand.CommandText.IndexOf(" ") > 0) ? CommandType.Text : CommandType.StoredProcedure;
            // 读取OleDbServer存储过程参数
            DataSet dsParameter = null;
            if ((pRefCommand.CommandType == CommandType.StoredProcedure) && ((pInParams != null) || (pOutParams != null)))
            {
                dsParameter = this.ExecuteQuery(string.Format("SELECT A.NAME, A.PREC, A.SCALE FROM SYS.SYSCOLUMNS A, SYS.PROCEDURES B WHERE A.ID=B.OBJECT_ID AND B.NAME='{0}' ORDER BY A.COLID", pRefCommand.CommandText));
                if (dsParameter == null || dsParameter.Tables[0].Rows.Count <= 0)
                {
                    return;
                }
            }
            int iPar = 0;
            // 设置输入参数集
            if (pInParams != null)
            {
                for (int i = 0; i < pInParams.Length; i++)
                {
                    OleDbParameter myParameter = new OleDbParameter();
                    myParameter.ParameterName = (dsParameter != null) ? dsParameter.Tables[0].Rows[iPar++][0].ToString() : string.Format("@P{0}", i);
                    if (pTable == null) // 不需自动识别列名
                    {
                        myParameter.Value = pInParams[i];
                    }
                    else if (Convert.GetTypeCode(pInParams[i]) != TypeCode.String) // 非字符串类型
                    {
                        myParameter.Value = pInParams[i];
                    }
                    else if (pTable.Columns.IndexOf(pInParams[i].ToString()) < 0) // 非表列名
                    {
                        myParameter.Value = pInParams[i];
                    }
                    else // 是表列名
                    {
                        myParameter.SourceColumn = (string)pInParams[i];
                    }
                    pRefCommand.Parameters.Add(myParameter);
                }
            }
            // 设置输出参数集
            if (pOutParams != null)
            {
                for (int i = 0; i < pOutParams.Length; i++)
                {
                    OleDbParameter myParameter = new OleDbParameter();
                    myParameter.ParameterName = (dsParameter != null) ? dsParameter.Tables[0].Rows[iPar++][0].ToString() : string.Format("@OP{0}", i);
                    myParameter.DbType = this.GetDbType(Convert.GetTypeCode(pOutParams[i]));
                    myParameter.Direction = ParameterDirection.Output;
                    if (pTable != null)
                    {
                        if (Convert.GetTypeCode(pOutParams[i]) == TypeCode.String)
                        {
                            if (pTable.Columns.IndexOf(pOutParams[i].ToString()) >= 0)
                            {
                                // 是表列名
                                myParameter.SourceColumn = (string)pOutParams[i];
                                myParameter.DbType = this.GetDbType(Type.GetTypeCode(pTable.Columns[(string)pOutParams[i]].DataType));
                            }
                        }
                    }
                    pRefCommand.Parameters.Add(myParameter);
                }
            }
        }

        /// <summary>
        /// 获得对象对应的DbType
        /// </summary>
        /// <param name="pTypeCode">TypeCode</param>
        /// <returns>DbType</returns>
        private DbType GetDbType(TypeCode pTypeCode)
        {
            DbType dbType = DbType.Object;
            switch (pTypeCode)
            {
                case TypeCode.Int32:
                    dbType = DbType.Int32;
                    break;
                case TypeCode.Int64:
                    dbType = DbType.Int64;
                    break;
                case TypeCode.Int16:
                    dbType = DbType.Int16;
                    break;
                case TypeCode.String:
                    dbType = DbType.String;
                    break;
                case TypeCode.Decimal:
                    dbType = DbType.Decimal;
                    break;
                case TypeCode.Double:
                    dbType = DbType.Double;
                    break;
                case TypeCode.Single:
                    dbType = DbType.Single;
                    break;
                case TypeCode.DateTime:
                    dbType = DbType.DateTime;
                    break;
                case TypeCode.Boolean:
                    dbType = DbType.Boolean;
                    break;
                default:
                    dbType = DbType.Object;
                    break;
            }
            return dbType;
        }

        #endregion

        #region 释放资源

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (mTransaction != null)
            {
                mTransaction.Dispose();
            }
            if (mConnection != null)
            {
                mConnection.Dispose();
            }
            mTransaction = null;
            mConnection = null;
            mStrConnectionString = null;
        }

        #endregion

        #region 事务处理

        /// <summary>
        /// 开始事务处理
        /// </summary>
        /// <returns>返回数据库事务处理对象或空(开始事务失败)</returns>
        public DOleDbTrans BeginTrans()
        {
            if (mTransaction != null) // 自己就是数据库事务处理对象
            {
                return null;
            }
            // 创建数据库事务处理对象
            DOleDbTrans myDOleDbTrans = new DOleDbTrans(mStrConnectionString);
            if (myDOleDbTrans.mTransaction == null) // 开始事务处理失败
            {
                myDOleDbTrans = null;
            }
            return myDOleDbTrans;
        }

        #endregion
    }


    /// <summary>
    /// 数据库操作管理类(OleDbServer),带事务处理,继承DOleDb类
    /// </summary>
    public class DOleDbTrans : DOleDb
    {
        #region 事务处理

        /// <summary>
        /// 构造函数,开始事务处理
        /// </summary>
        public DOleDbTrans(string pStrConnectionString)
            : base(pStrConnectionString)
        {
            mConnection = new OleDbConnection(mStrConnectionString);
            try
            {
                // 开始事务处理
                mConnection.Open();
                mTransaction = mConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            }
            catch
            {
                this.Dispose();
            }
        }

        /// <summary>
        /// 提交事务处理
        /// </summary>
        public bool Commit()
        {
            if (mTransaction == null)
            {
                return false;
            }
            try
            {
                mTransaction.Commit();
                this.Dispose();
                return true;
            }
            catch
            {
                this.Rollback();
                return false;
            }
        }

        /// <summary>
        /// 回滚事务处理
        /// </summary>
        public void Rollback()
        {
            if (mTransaction != null)
            {
                try
                {
                    mTransaction.Rollback();
                }
                catch { }
                this.Dispose();
            }
        }

        #endregion
    }
}
